from sqlalchemy import Column,String,Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import desc
from sqlalchemy.ext.automap import automap_base	
from sqlalchemy.orm import Session
from sqlalchemy import create_engine
import datetime
import json
import math
import time
import redis
# import numpy as np
# import pandas as pd
# from collections import defaultdict
# import copy

print('start')
Base = automap_base()
engine = create_engine('sqlite:///car.db')
Base.prepare(engine, reflect=True)	

session = Session(engine)

# #建立数据库映射
# efence=Base.classes.car_app_efence
# alarm=Base.classes.car_app_alarm

# alarm1='13050038101stop_alarm2021-04-19 08:39:40'
# # alarm1='13050038101efence_alarm2021-04-16 16:41:38'
# res=session.query(alarm).filter(alarm.name==alarm1).all()

# for x in res:
# 	print(vars(x))
# # from math import pi,sin,cos
# data=[{'name': '13050038101stop_alarm2021-04-19 08:39:40', 'device_id': '13050038101', 'location': "{'satelite': [114.036939, 22.613083]}", 'content': '停留限制：60000,实际停留：0', 'kind': 'stop_alarm', 'handle': 'auto_end', 's_time': '2021-04-19 08:39:40', 'e_time': '2021-04-19 09:26:30', 'remark': '0', 'owner': 'demo'}]
# '_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x7f60cbdf03d0>, 'owner': 'demo', 'handle': 'no', 's_time': '2021-04-19 08:39:40', 'content': '停留限制：60000,实际停留：140031', 'device_id': '13050038101', 'id': 20, 'remark': '0', 'e_time': '0', 'kind': 'stop_alarm', 'location': '0', 'name': '13050038101stop_alarm2021-04-19 08:39:40'}
# session.bulk_update_mappings(alarm,data)
# def get_distance(point1,point2):
# 	lat1=point1[1]
# 	lat2=point2[1]
# 	lng1=point1[0]
# 	lng2=point2[0]
# 	EARTH_REDIUS = 6378.137

# 	def rad(d):
# 		return d * pi / 180.0

# 	radLat1 = rad(lat1)
# 	radLat2 = rad(lat2)
# 	lat_a = radLat1 - radLat2
# 	lng_b = rad(lng1) - rad(lng2)
# 	result_s = 2 * math.asin(math.sqrt(math.pow(sin(lat_a/2), 2) + cos(radLat1) * cos(radLat2) * math.pow(sin(lng_b/2), 2)))
# 	result_s = result_s * EARTH_REDIUS

# 	return result_s

# points=session.query(efence).filter(efence.name=='test').first().content
# points=eval(points)
# points=np.array(points)
# pt=[115.7033992567,26.09908511]
# def get_polyline_distance(points,lng,lat):
	

# 	t_lng=lng* pi / 180.0
# 	t_lat=lat* pi / 180.0
	
# 	radLat1 = points[:,1:2]*pi/180
# 	radLat2 = 26.09908511* pi / 180.0

# 	lng_b=points[:,0:1]*pi/180-t_lng
# 	lat_a=points[:,1:2]*pi/180-t_lat
# 	distance_list=2*np.arcsin(np.sqrt(np.power(np.sin(lat_a/2),2)+np.cos(radLat1)*cos(radLat2)*np.power(np.sin(lng_b/2),2)))*6378.137

# 	return distance_list
# # result_s = 2 * math.asin(math.sqrt(math.pow(sin(lat_a[0]/2), 2) + cos(radLat1) * cos(radLat2) * math.pow(sin(lng_b[0]/2), 2)))
# eee=get_distance(pt,[114.254345, 22.725787])
# ddd=get_polyline_distance(points,115.7033992567,26.09908511)

# print('eee',eee)
# print(ddd.min(),ddd.min()>403,ddd)

#连接REDIS

import random


pool_redis= redis.ConnectionPool(host='localhost',port=6379,decode_responses=True)
mesg_redis=redis.Redis(connection_pool=pool_redis)


def get_simu(org):
	min_num=-0.0001
	max_num=0.0009
	min_lng=100.394801
	min_lat=24.913303
	max_lng=118.394801
	max_lat=39.913303
	simu={}
	lng=org['lng']
	lat=org['lat']
	speed=['0','11.1','77.7','25.5','30','40','50','60']
	org['lng']=random.uniform(min_num,max_num)+lng
	org['lat']=random.uniform(min_num,max_num)+lat
	# org['lng']=random.uniform(min_num,max_num)+114.064552
	# org['lat']=random.uniform(min_num,max_num)+22.548377

	org['speed']=speed[random.randint(0,7)]
	org['speed']='0'
	org['serv_receive']=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
	org['dev_upload']=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

	return org


t={'lbs': '0', 'mileage': 220.0, 'speed': '5', 'othermesg': "{'on_off_line': 'online', 'receive_port': [10808, 'rby808'], 'alarm': [], 'voltage': 12.06, 'gps_num': 12, 'hight': '0'}", 'wifi': '0', 'oil_use': '0', 'dirct': '0', 'scanner': '0', 'rssi': 22, 'efence_alarm': '0', 'status': "['acc_on', 'track', 'oil_normal', 'power_normal','idling']", 'remark': '0', 'heart_rate': '0', 'speed_alarm': '0', 'battery': '0', 'blood_pres': '0', 'stop_time': '0', 'sos_alarm': '0', 'step_count': '0', 'heart_alarm': '0', 'device_id': '66666', 'dev_upload': '2021-04-15 16:27:26', 'obd': '0', 'blood_alarm': '0', 'lng': 114.034895, 'serv_receive': '2021-04-15 16:27:27', 'track_type': 'satelite', 'location': '0', 'lat': 22.613069, 'hard_verson': '0'}
d={'lbs': '0', 'mileage': 151255.7, 'speed': '0', 'othermesg': "{'on_off_line': 'online', 'receive_port': [10808, 'rby808'], 'alarm': [], 'hight': '0'}", 'wifi': '0', 'oil_use': '0', 'dirct': '0', 'scanner': '0', 'rssi': '0', 'efence_alarm': '0', 'status': "['acc_on', 'un_track', 'oil_normal', 'power_normal']", 'remark': '0', 'heart_rate': '0', 'speed_alarm': '0', 'battery': '0', 'blood_pres': '0', 'stop_time': '0', 'sos_alarm': '0', 'step_count': '0', 'heart_alarm': '0', 'device_id': '11111', 'dev_upload': '2021-04-15 16:09:49', 'obd': '0', 'blood_alarm': '0', 'lng': 117.7378, 'serv_receive': '2021-04-15 16:09:51', 'track_type': 'satelite', 'location': '0', 'lat': 38.996684, 'hard_verson': '0'}


for x in range(2):
	t=get_simu(t)
	d=get_simu(d)
	mesg_redis.rpush('car_gps_data',str(t))
	mesg_redis.rpush('car_gps_data',str(d))
	print('sleep 2')
	time.sleep(2)


alarm_data=mesg_redis.lrange('car_alarm_to_db',0,-1)
print('alarm_data------',len(alarm_data),alarm_data)
# 销毁已经获取的原始数据
mesg_redis.ltrim('car_alarm_to_db',len(alarm_data),-1)


# def time_cacu(days=1):
# 	now = time.time()
# 	midnight = now - (now % 86400) + time.timezone
# 	e_time=midnight
# 	s_time = midnight - 86400*int(days)

# 	s_time = datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(s_time)), "%Y-%m-%d %H:%M:%S")
# 	e_time = datetime.datetime.strptime(time.strftime("%Y-%m-%d %H:%M:%S", time.localtime(e_time)), "%Y-%m-%d %H:%M:%S")
# 	s_time=str(s_time)
# 	e_time=str(e_time)

# 	return [s_time,e_time]

# print(time_cacu(5))